package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.DocumentosDao;
import com.sophossystems.client.asmon.dto.DocumentosDTO;
import com.sophossystems.client.asmon.dto.DocumentosPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.Documentos;

public class DocumentosDaoImpl extends AbstractDAO implements  DocumentosDao
{
	private static Logger log = Logger.getLogger(DocumentosDaoImpl.class);
	
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public DocumentosPkDTO insert(final DocumentosDTO dto){
		DocumentosPkDTO exito =null ;
	try {
		 exito = (DocumentosPkDTO) getHibernateTemplate().execute(new HibernateCallback() {
			/**
			 * {@inheritDoc}
			 * 
			 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
			 */
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
			    Transaction tx = session.beginTransaction();
			    DocumentosPkDTO pk = null;
			    Documentos documentos = new Documentos();
			    documentos.mapper(dto);
			    session.save(documentos);
			    tx.commit();
			    
			    try{
			    	DocumentosPkDTO res = findLastInsert();
			    	pk = new DocumentosPkDTO();
			    	pk.setIdCliente(res.getIdCliente());
			    	
			    }catch (DaoException es) {
					
					throw new HibernateException("Insersion: " +es.getMessage());
				}

				return pk;
			
				
			}

			
		});
	///	 return exito;
	} catch (final Exception e) {
		logger.error("Error: getActivity, cause: " + e.getMessage(), e);
		
	}
	return exito;
}

		
	/** 
	 * Updates a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void update(final DocumentosDTO dto) throws DaoException
	{
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					DocumentosPkDTO pk = null;
					DocumentosDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdCliente());
						pk = new DocumentosPkDTO();
						pk.setIdCliente(res.getIdCliente());
					} catch (DaoException es) {
						throw new HibernateException(
								"Update ActivityDao: "
										+ es.getMessage());
					}
					///aqui se actualizar los campos y con los de la entrada  
					//cuidado de actualiza todo.
					Documentos persis = new Documentos();
					persis.mapper(dto);
					persis.setIdCliente(res.getIdCliente());
					session.update(persis);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: Actualidar Documentos, cause: " + e.getMessage(), e);
			throw new DaoException("Error: Actualidar Documentos, cause: " + e.getMessage());
		}
	
		
	
	}

	/** 
	 * Deletes a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void delete(final DocumentosPkDTO pk) throws DaoException
	{
		
		
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				    Transaction tx = session.beginTransaction();
				    Documentos documentos = (Documentos)session.load(Documentos.class,pk.getIdCliente() );
				    session.delete(documentos);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: " + e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "Documentos";
	}

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'idCliente = :idCliente'.
	 */
	@Override
	@Transactional
	public DocumentosDTO findByPrimaryKey(final int idCliente) 
			throws DaoException	{
		DocumentosDTO documentos = null;
		try {
			documentos = (DocumentosDTO)getHibernateTemplate().execute(new
					HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						
							Query oQuery = session.createQuery("SELECT doc.idCliente as idCliente,doc.idClienteGeneral as idClienteGeneral, doc.actaCons as actaCons, doc.actaNac as actaNac," +
									" doc.identRepLegal as identRepLegal, doc.identOficial as identOficial, doc.compDomicilio as compDomicilio, doc.rfc as rfc, doc.curp as curp, doc.fea as fea, doc.ciec as ciec," +
									" doc.altaPatronal as altaPatronal, doc.otro as otro, doc.altaShcp as altaShcp FROM " + getTableName() +
									" doc WHERE doc.idCliente = :id_cliente");
							oQuery.setInteger("id_cliente", idCliente);
							DocumentosDTO  documentosDto = (DocumentosDTO) oQuery.setResultTransformer(
									Transformers.aliasToBean(DocumentosDTO.class)).list().get(0);
							return documentosDto;
						}
					});

		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;
	}

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria ''.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<DocumentosDTO> findAll() 
			throws DaoException	{
		List<DocumentosDTO> documentos = null;
				try {
					documentos = (List<DocumentosDTO>)getHibernateTemplate().execute(new
							HibernateCallback() {
								
								@Override
								public Object doInHibernate(Session session) throws HibernateException,
										SQLException {
								
									Query oQuery = session.createQuery("SELECT doc.idCliente as idCliente,doc.idClienteGeneral as idClienteGeneral, doc.actaCons as actaCons, doc.actaNac as actaNac," +
											" doc.identRepLegal as identRepLegal, doc.identOficial as identOficial, doc.compDomicilio as compDomicilio, doc.rfc as rfc, doc.curp as curp, doc.fea as fea, doc.ciec as ciec," +
											" doc.altaPatronal as altaPatronal, doc.otro as otro, doc.altaShcp as altaShcp FROM " 
											+ getTableName() + " doc ORDER BY doc.idCliente");
									List<DocumentosDTO>  documentosDto = oQuery.setResultTransformer(
											Transformers.aliasToBean(DocumentosDTO.class)).list();
									return documentosDto;
								}
							});

		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;
	}

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'idCliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<DocumentosDTO> findWhereIdClienteEquals(final int idCliente)
			throws DaoException	{
		List<DocumentosDTO> documentos = null;
				try {
					documentos = (List<DocumentosDTO>)getHibernateTemplate().execute(new
							HibernateCallback() {
								
								@Override
								public Object doInHibernate(Session session) throws HibernateException,
										SQLException {
								
									Query oQuery = session.createQuery("SELECT doc.idCliente as idCliente, doc.actaCons as actaCons, doc.actaNac as actaNac," +
											" doc.identRepLegal as identRepLegal, doc.identOficial as identOficial, doc.compDomicilio as compDomicilio, doc.rfc as rfc, doc.curp as curp, doc.fea as fea, doc.ciec as ciec," +
											" doc.altaPatronal as altaPatronal, doc.otro as otro, doc.altaShcp as altaShcp FROM "
											+ getTableName() + " doc WHERE doc.idCliente = :id_cliente ORDER BY doc.idCliente");
									oQuery.setInteger(0, idCliente);
									List<DocumentosDTO>  documentosDto = oQuery.setResultTransformer(
											Transformers.aliasToBean(DocumentosDTO.class)).list();
									return documentosDto;
								}
							});

		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;
	}

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'actaCons = :actaCons'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereActaConsEquals(int actaCons) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE actaCons = ? ORDER BY actaCons", this,actaCons);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'actaNac = :actaNac'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereActaNacEquals(int actaNac) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE actaNac = ? ORDER BY actaNac", this,actaNac);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'identRepLegal = :identRepLegal'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereIdentRepLegalEquals(int identRepLegal) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE identRepLegal = ? ORDER BY identRepLegal", this,identRepLegal);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'identOficial = :identOficial'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereIdentOficialEquals(int identOficial) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE identOficial = ? ORDER BY identOficial", this,identOficial);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'compDomicilio = :compDomicilio'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereCompDomicilioEquals(int compDomicilio) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE compDomicilio = ? ORDER BY compDomicilio", this,compDomicilio);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'rfc = :rfc'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereRfcEquals(int rfc) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE rfc = ? ORDER BY rfc", this,rfc);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'curp = :curp'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereCurpEquals(int curp) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE curp = ? ORDER BY curp", this,curp);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'fea = :fea'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereFeaEquals(int fea) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE fea = ? ORDER BY fea", this,fea);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'ciec = :ciec'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereCiecEquals(int ciec) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE ciec = ? ORDER BY ciec", this,ciec);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'altaPatronal = :altaPatronal'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereAltaPatronalEquals(int altaPatronal) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE altaPatronal = ? ORDER BY altaPatronal", this,altaPatronal);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'otro = :otro'.
	 
	@Transactional
	public List<DocumentosDTO> findWhereOtroEquals(String otro) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idCliente, actaCons, actaNac, identRepLegal, identOficial, compDomicilio, rfc, curp, fea, ciec, altaPatronal, otro FROM " + getTableName() + " WHERE otro = ? ORDER BY otro", this,otro);
		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns the rows from the DOCUMENTOS table that matches the specified primary-key value.
	 */
	public DocumentosDTO findByPrimaryKey(DocumentosPkDTO pk) throws DaoException
	{
		return findByPrimaryKey( pk.getIdCliente() );
	}
	
	
	/** 
	 * Returns all rows from the DOCUMENTOS table that match the criteria 'idCliente = :idCliente'.
	 */
	
	@Override
	@Transactional
	public DocumentosDTO findLastInsert(final int idCliente)
			throws DaoException	{
		DocumentosDTO documentos = null;
				try {
					documentos = (DocumentosDTO)getHibernateTemplate().execute(new
							HibernateCallback() {
								
								@Override
								public Object doInHibernate(Session session) throws HibernateException,
										SQLException {
									
									Query oQuery = session.createQuery("SELECT max(idCliente) as idCliente FROM "+getTableName());
									
									DocumentosDTO  documentosDto = (DocumentosDTO) oQuery.setResultTransformer(
											Transformers.aliasToBean(DocumentosDTO.class)).list().get(0);
									return documentosDto;
								}
							});

		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;
	}

	@Override
	public DocumentosPkDTO findLastInsert() throws DaoException {
		DocumentosPkDTO documentos = null;
		try {
			documentos = (DocumentosPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(d.idCliente) as idCliente FROM "
											+ getTableName()
											+ " d ");
							
							DocumentosPkDTO documentosDto = (DocumentosPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(DocumentosPkDTO.class)).list().get(0);
							return documentosDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: DocumentosDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;

	}


	@Override
	public DocumentosDTO findByIdClienteGeneral(final int idClienteGeneral)
			throws DaoException {
		DocumentosDTO documentos = null;
		try {
			documentos = (DocumentosDTO)getHibernateTemplate().execute(new
					HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						
							Query oQuery = session.createQuery("SELECT doc.idCliente as idCliente, doc.idClienteGeneral as idClienteGeneral,doc.actaCons as actaCons, doc.actaNac as actaNac," +
									" doc.identRepLegal as identRepLegal, doc.identOficial as identOficial, doc.compDomicilio as compDomicilio, doc.rfc as rfc, doc.curp as curp, doc.fea as fea, doc.ciec as ciec," +
									" doc.altaPatronal as altaPatronal, doc.otro as otro, doc.altaShcp as altaShcp FROM " + getTableName() +
									" doc WHERE doc.idClienteGeneral = :idClienteGeneral");
							oQuery.setInteger("idClienteGeneral", idClienteGeneral);
							DocumentosDTO  documentosDto = (DocumentosDTO) oQuery.setResultTransformer(
									Transformers.aliasToBean(DocumentosDTO.class)).list().get(0);
							return documentosDto;
						}
					});

		}
		catch (Exception e) {
			logger.error("Error: DOCUMENTOS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return documentos;

	}

}
